import os
import re
import pandas as pd
import tkinter as tk
from tkinter import ttk
from tkinter import filedialog
from tkinter import messagebox
from datetime import datetime, timedelta
import threading


class ExcelProcessorApp:

    # 正则表达式提取文件名称中的日期
    def extract_date(text):
        date_pattern = r'\d{1,4}\d{1,2}\d{1,4}'
        matches = re.findall(date_pattern, text)
        return matches[0]
    def generate_next_id(current_id):
        # 分离出前缀和数字部分
        # 假设前缀后面紧跟着一串0
        prefix, number_str = current_id.split('0', 1)
        # 将数字部分转换为整数
        number = int(number_str)
        # 递增数字并格式化回原样（保持足够的0）
        next_number = number + 1
        # 假设数字部分总共是12位，不足部分用0填充
        next_number_str = format(next_number, '012d')
        # 合并前缀和数字部分生成新的ID
        next_id = prefix + '0' * (12 - len(next_number_str)) + next_number_str
        return next_id
    def convert_seconds_to_dhms(total_seconds, multiplier=0.36):
        # 应用乘数
        adjusted_seconds = total_seconds * multiplier
        # 计算天数
        days = int(adjusted_seconds // 86400)
        # 计算剩余秒数
        remainder = adjusted_seconds % 86400
        # 计算小时数
        hours = int(remainder // 3600)
        # 再次计算剩余秒数
        remainder = remainder % 3600
        # 计算分钟数
        minutes = int(remainder // 60)
        # 计算秒数
        seconds = int(remainder % 60)
        formatted_time = f"+ {days}d {hours}h {minutes}m {seconds}s"
        return formatted_time
    def __init__(self, root):
        # 获取当前执行脚本的路径（无论是.py还是.exe）
        current_directory = os.getcwd()
        # 默认根目录
        self.baseDir = current_directory
        self.root = root
        self.root.title("Excel Processing")

        self.label = tk.Label(root, text="Processing Excel Files")
        self.label.pack(pady=10)

        # 输入目录选择
        self.input_label = tk.Label(root, text="Input Directory:")
        self.input_label.pack(pady=5)
        self.input_value = tk.StringVar()
        self.input_value.set(self.baseDir + "/Input")
        self.input_entry = tk.Entry(root, width=50, textvariable=self.input_value)
        self.input_entry.pack(pady=5)
        self.input_button = tk.Button(root, text="Browse", command=self.browse_input)
        self.input_button.pack(pady=5)

        # 输出目录选择
        self.output_label = tk.Label(root, text="Output Directory:")
        self.output_label.pack(pady=5)
        self.output_value = tk.StringVar()
        self.output_value.set(self.baseDir + "/Output")
        self.output_entry = tk.Entry(root, width=50, textvariable=self.output_value)
        self.output_entry.pack(pady=5)
        self.output_button = tk.Button(root, text="Browse", command=self.browse_output)
        self.output_button.pack(pady=5)

        self.progress = ttk.Progressbar(root, orient="horizontal", length=300, mode="determinate", maximum=100)
        self.progress.pack(pady=10)
        self.progress['value'] = 0
        self.label = tk.Label(root, text="")
        self.label.pack(pady=5)

        self.start_button = tk.Button(root, text="Start", command=self.start_processing)
        self.start_button.pack(pady=5)

    def browse_input(self):
        directory = filedialog.askdirectory()
        if directory:
            self.input_entry.delete(0, tk.END)
            self.input_entry.insert(0, directory)

    def browse_output(self):
        directory = filedialog.askdirectory()
        if directory:
            self.output_entry.delete(0, tk.END)
            self.output_entry.insert(0, directory)
    def start_processing(self):
        try:
            self.label.config(text="Please wait...")
            self.start_button.config(state=tk.DISABLED)
            self.thread = threading.Thread(target=self.process_files)
            self.thread.start()
            self.root.after(100, self.check_thread)
        except Exception as e:
            messagebox.showerror("Alert!", e)
    def process_files(self):

        input_dir = self.input_entry.get()
        output_dir = self.output_entry.get()
        # 判断是否选择文件
        if not input_dir or not output_dir :
            messagebox.showinfo("Alert!", "Please provide both input and output directories.")
            return
        self.label.config(text = 'Loading...')
        self.root.update_idletasks()
        # 获取DC列表
        dc_input_dir_path = os.path.join(input_dir, "OMP DC.xlsx")
        if not os.path.exists(dc_input_dir_path):
            messagebox.showerror("Alert!", "File OMP DC.xlsx does not exist! ")
            return
        dc_input_df = pd.read_excel(dc_input_dir_path, engine='openpyxl')
        dc_values = dc_input_df["DC"].unique()
        # 获取dc_rebalancing列表
        dc_rebalancing_input_dir_path = os.path.join(input_dir, "DC Rebalancing.xlsx")
        if not os.path.exists(dc_rebalancing_input_dir_path):
            messagebox.showerror("Alert!", "File DC Rebalancing.xlsx does not exist! ")
            return
        dc_rebalancing_input_df = pd.read_excel(dc_rebalancing_input_dir_path, engine='openpyxl')
        dc_rebalancing_input_df['key'] = list(zip(dc_rebalancing_input_df['FROM'], dc_rebalancing_input_df['TO']))
        # 获取ZC564列表
        zc564_input_dir_path = os.path.join(input_dir, "ZC564.xls")
        if not os.path.exists(zc564_input_dir_path):
            messagebox.showerror("Alert!", "File ZC564.xls does not exist! ")
            return
        zc564_input_df = pd.read_excel(zc564_input_dir_path)
        zc564_values = zc564_input_df["MRP Controller"].unique()
        # 获取APO列表
        apo_input_dir_path = os.path.join(input_dir, "APO Transportation Lane")
        if not os.path.exists(apo_input_dir_path):
            messagebox.showerror("Alert!", "Directory APO Transportation Lane does not exist! ")
            return
        apo_input_files = [f for f in os.listdir(apo_input_dir_path) if f.lower().endswith('.xlsx') or f.lower().endswith('.xls')]
        total_files = len(apo_input_files)
        if total_files == 0 :
            messagebox.showerror("Alert!", "File APO Transportation Lane YYYYMMDD.xlsx does not exist! ")
            return
        for idx, filename in enumerate(apo_input_files):
            apo_input_file_path = os.path.join(apo_input_dir_path, filename)
            apo_input_df = pd.read_excel(apo_input_file_path, engine='openpyxl')
            # 过滤Source与Plant列等于DC值
            apo_matched_df = apo_input_df[(apo_input_df['Source'].isin(dc_values)) & (apo_input_df['Plant'].isin(dc_values))]
            newfilename = filename.split(".")[0]
            suffix = filename.split(".")[1]
            # 获取日期
            fileDateName = ExcelProcessorApp.extract_date(filename)
            # APO hair源数据处理
            hair_apo_output_dir = output_dir + "/Hair/" + fileDateName
            if not os.path.exists(hair_apo_output_dir):
                os.makedirs(hair_apo_output_dir)
            hairFileName =  "Hair " + newfilename + "." + suffix
            output_hair_apo_file_path = os.path.join(hair_apo_output_dir, hairFileName)
            hair_apo_matched_df = apo_matched_df[apo_matched_df['Category'] == "HAIRCARE"]
            try:
                hair_apo_matched_df.to_excel(output_hair_apo_file_path, index=False, engine='openpyxl')
            except Exception as e:
                messagebox.showerror('Alert!', e)
                return
            self.progress['value'] = (idx + 1) / total_files * 15
            self.label.config(text = 'Hair APO Transportation Lane successfully')
            self.root.update_idletasks()
            # APO PCC 源数据处理
            pcc_apo_output_dir = output_dir + "/PCC/" + fileDateName
            if not os.path.exists(pcc_apo_output_dir):
                os.makedirs(pcc_apo_output_dir)
            pccFileName = "PCC " + newfilename + "." + suffix
            pcc_apo_matched_df = apo_matched_df[apo_matched_df['Category'] == "PCC"]
            output_pcc_apo_file_path = os.path.join(pcc_apo_output_dir, pccFileName)
            try:
                pcc_apo_matched_df.to_excel(output_pcc_apo_file_path, index=False, engine='openpyxl')
            except Exception as e:
                messagebox.showerror('Alert!', e)
                return
            self.progress['value'] = (idx + 1) / total_files * 30
            self.label.config(text = 'PCC APO Transportation Lane successfully')
            self.root.update_idletasks()
            # Hair BU REFTransport
            hair_apo_input_df = pd.read_excel(output_hair_apo_file_path, engine='openpyxl')
            new_rows = []
            for index, row in hair_apo_input_df.iterrows():
                today = datetime.now().strftime('%Y%m%d%H%M%S')
                # 将Valid From Date (TRPROD)和Valid To Date (TRPROD)列转换为字符串
                fromDate = row['Valid From Date (TRPROD)']
                toDate = row['Valid To Date (TRPROD)']
                # 筛选条件：Valid From Date (TRPROD)大于今天 或 Valid To Date (TRPROD)不等于'9999*'
                flag = (fromDate > int(today)) | (str(toDate).startswith('9999'))
                comments=  ''
                if flag: comments = 'Sourcing change from ' + str(fromDate)[:-4]
                new_row = {
                    'TransportId': ExcelProcessorApp.generate_next_id("H0000000000" + str(index)),
                    'FromLocationId': row['Source'],
                    'ToLocationId': row['Plant'],
                    'ProductId': row['Material'],
                    'Comments': comments,
                    'Tlane LT': row['Tlane LT'],
                    'FromDate': str(fromDate)[:-4]
                }
                new_rows.append(new_row)
            hair_apo_input_df['key'] = list(zip(hair_apo_input_df['Source'], hair_apo_input_df['Plant']))
            keys = dc_rebalancing_input_df["key"].unique()
            merged_df = hair_apo_input_df[(hair_apo_input_df['key'].isin(keys))]
            filtered_df = merged_df.dropna(subset=['key'])
            filtered_df = filtered_df[hair_apo_input_df.columns].drop(columns=['key'])
            new_rows_len = len(new_rows)
            for index, row in filtered_df.iterrows():
                fromDate = row['Valid From Date (TRPROD)']
                new_row = {
                    'TransportId': ExcelProcessorApp.generate_next_id("H0000000000" + str(new_rows_len + index)),
                    'FromLocationId': row['Source'],
                    'ToLocationId': row['Plant'],
                    'ProductId': row['Material'],
                    'Comments': 'DC Rebalancing',
                    'Tlane LT': '',
                    'FromDate': str(fromDate)[:-4]
                }
                new_rows.append(new_row)
            hair_REFTransport_df = pd.DataFrame(new_rows)
            hair_REFTransport_output_dir = output_dir + "/Hair/" + fileDateName
            output_hair_REFTransport_file_path = os.path.join(hair_REFTransport_output_dir, "Hair REFTransport " + fileDateName + ".xlsx")
            try:
                hair_REFTransport_df[hair_REFTransport_df.columns].drop(columns=['Tlane LT', 'FromDate']).to_excel(output_hair_REFTransport_file_path, index=False, engine='openpyxl')
            except Exception as e:
                messagebox.showerror('Alert!', e)
                return
            self.progress['value'] = (idx + 1) / total_files * 40
            self.label.config(text = 'Hair REFTransport successfully')
            self.root.update_idletasks()
            # PCC BU REFTransport
            pcc_apo_input_df = pd.read_excel(output_pcc_apo_file_path, engine='openpyxl')
            new_rows = []
            for index, row in pcc_apo_input_df.iterrows():
                today = datetime.now().strftime('%Y%m%d%H%M%S')
                # 将Valid From Date (TRPROD)和Valid To Date (TRPROD)列转换为字符串
                fromDate = row['Valid From Date (TRPROD)']
                toDate = row['Valid To Date (TRPROD)']
                # 筛选条件：Valid From Date (TRPROD)大于今天 或 Valid To Date (TRPROD)不等于'9999*'
                flag = (fromDate > int(today)) | (str(toDate).startswith('9999'))
                comments=  ''
                if flag: comments = 'Sourcing change from ' + str(fromDate)[:-4]
                new_row = {
                    'TransportId': ExcelProcessorApp.generate_next_id("P0000000000" + str(index)),
                    'FromLocationId': row['Source'],
                    'ToLocationId': row['Plant'],
                    'ProductId': row['Material'],
                    'Comments': comments,
                    'Tlane LT': row['Tlane LT'],
                    'FromDate': str(fromDate)[:-4]
                }
                new_rows.append(new_row)
            pcc_apo_input_df['key'] = list(zip(pcc_apo_input_df['Source'], pcc_apo_input_df['Plant']))
            merged_df = pcc_apo_input_df[(pcc_apo_input_df['key'].isin(keys))]
            filtered_df = merged_df.dropna(subset=['key'])
            filtered_df = filtered_df[pcc_apo_input_df.columns].drop(columns=['key'])
            new_rows_len = len(new_rows)
            for index, row in filtered_df.iterrows():
                fromDate = row['Valid From Date (TRPROD)']
                new_row = {
                    'TransportId': ExcelProcessorApp.generate_next_id("P0000000000" + str(new_rows_len + index)),
                    'FromLocationId': row['Source'],
                    'ToLocationId': row['Plant'],
                    'ProductId': row['Material'],
                    'Comments': 'DC Rebalancing',
                    'Tlane LT': '',
                    'FromDate': str(fromDate)[:-4]
                }
                new_rows.append(new_row)
            pcc_REFTransport_df = pd.DataFrame(new_rows)
            pcc_REFTransport_output_dir = output_dir + "/PCC/" + fileDateName
            output_pcc_REFTransport_file_path = os.path.join(pcc_REFTransport_output_dir, "PCC REFTransport " + fileDateName + ".xlsx")
            try:
                pcc_REFTransport_df[pcc_REFTransport_df.columns].drop(columns=['Tlane LT', 'FromDate']).to_excel(output_pcc_REFTransport_file_path, index=False, engine='openpyxl')
            except Exception as e:
                messagebox.showerror('Alert!', e)
                return
            self.progress['value'] = (idx + 1) / total_files * 50
            self.label.config(text = 'PCC REFTransport successfully')
            self.root.update_idletasks()
            # Hair REFTransportType
            new_rows = []
            for index, row in hair_REFTransport_df.iterrows():
                if row['Comments'] == 'DC Rebalancing':
                    TransportOffset = 'DC Rebalancing'
                    TLaneType = 'Rebalance'
                else:
                    TransportOffset = ExcelProcessorApp.convert_seconds_to_dhms(row['Tlane LT'])
                    TLaneType = ''
                new_row = {
                        'TransportId': row['TransportId'],
                        'TransportType': 'Default',
                        'TLaneType': TLaneType,
                        'Preference': '10',
                        'Emergency': 'NO',
                        'TransportOffset': TransportOffset,
                        'GRPOffset': '',
                        'ExpCalendarId': '',
                        'TransportCalendarId': '',
                        'RecepCalendarId': ''
                }
                new_rows.append(new_row)
            hair_REFTransport_type_df = pd.DataFrame(new_rows)
            hair_REFTransport_type_output_dir = output_dir + "/Hair/" + fileDateName
            output_hair_REFTransport_file_path = os.path.join(hair_REFTransport_type_output_dir, "Hair REFTransportType " + fileDateName + ".xlsx")
            try:
                hair_REFTransport_type_df.to_excel(output_hair_REFTransport_file_path, index=False, engine='openpyxl')
            except Exception as e:
                messagebox.showerror('Alert!', e)
                return
            self.progress['value'] = (idx + 1) / total_files * 60
            self.label.config(text = 'Hair REFTransportType successfully')
            self.root.update_idletasks()
            # PCC REFTransportType
            new_rows = []
            for index, row in pcc_REFTransport_df.iterrows():
                if row['Comments'] == 'DC Rebalancing':
                    TransportOffset = 'DC Rebalancing'
                    TLaneType = 'Rebalance'
                else:
                    TransportOffset = ExcelProcessorApp.convert_seconds_to_dhms(row['Tlane LT'])
                    TLaneType = ''
                new_row = {
                    'TransportId': row['TransportId'],
                    'TransportType': 'Default',
                    'TLaneType': TLaneType,
                    'Preference': '10',
                    'Emergency': 'NO',
                    'TransportOffset': TransportOffset,
                    'GRPOffset': '',
                    'ExpCalendarId': '',
                    'TransportCalendarId': '',
                    'RecepCalendarId': ''
                }
                new_rows.append(new_row)
            pcc_REFTransport_type_df = pd.DataFrame(new_rows)
            pcc_REFTransport_type_output_dir = output_dir + "/PCC/" + fileDateName
            output_pcc_REFTransport_file_path = os.path.join(pcc_REFTransport_type_output_dir, "PCC REFTransportType " + fileDateName + ".xlsx")
            try:
                pcc_REFTransport_type_df.to_excel(output_pcc_REFTransport_file_path, index=False, engine='openpyxl')
            except Exception as e:
                messagebox.showerror('Alert!', e)
                return
            self.progress['value'] = (idx + 1) / total_files * 70
            self.label.config(text = 'PCC REFTransportType successfully')
            self.root.update_idletasks()
            # BU Sourcing Change
            new_rows = []
            for index, row in hair_REFTransport_df[hair_REFTransport_df['Comments'].str.contains('Sourcing change', na=False)].iterrows():
                PrefProcessId = 'TR/' + str(row['ProductId']) + '/' + row['FromLocationId'] + '/' + row['ToLocationId'] + '/Default'
                ProductLocationId = str(row['ProductId']) + '/' + row['FromLocationId']
                new_row = {
                    'SourcingDecisionId': ExcelProcessorApp.generate_next_id("HSD0000000000" + str(index)),
                    'Active': 'YES',
                    'COMMENTS': '',
                    'DataOrigin': 'DB',
                    'FromDate': row['FromDate'],
                    'InactiveReason': '',
                    'IsActive': 'YES',
                    'PID': 'DCs',
                    'MURESPONSIBLEGROUPID': zc564_values[0],
                    'PartitionId': 'DCs',
                    'Preference': '-1',
                    'PrefProcessId': PrefProcessId,
                    'ProductLocationId': ProductLocationId,
                    'StatusIcon': '',
                    'StatusMessage': '',
                    'APPLICATION': 'PLS',
                    'ObjectStatus': 'new',
                    'SID': '',
                    'SOURCE': 'ProjectSpecific',
                    'TXID': ''
                }
                new_rows.append(new_row)
            hair_sourcing_change_df = pd.DataFrame(new_rows)
            hair_sourcing_change_output_dir = output_dir + "/Hair/" + fileDateName
            output_hair_sourcing_change_file_path = os.path.join(hair_sourcing_change_output_dir, "Hair Sourcing Change " + fileDateName + ".xlsx")
            try:
                hair_sourcing_change_df.to_excel(output_hair_sourcing_change_file_path, index=False, engine='openpyxl')
            except Exception as e:
                messagebox.showerror("Alert!", e)
                return
            self.progress['value'] = (idx + 1) / total_files * 85
            self.label.config(text = 'Hair Sourcing change successfully')
            self.root.update_idletasks()
            new_rows = []
            for index, row in pcc_REFTransport_df[pcc_REFTransport_df['Comments'].str.contains('Sourcing change', na=False)].iterrows():
                PrefProcessId = 'TR/' + str(row['ProductId']) + '/' + row['ToLocationId'] + '/' + row['FromLocationId'] + '/Default'
                ProductLocationId = str(row['ProductId']) + '/' + row['ToLocationId']
                new_row = {
                    'SourcingDecisionId': ExcelProcessorApp.generate_next_id("HSD0000000000" + str(index)),
                    'Active': 'YES',
                    'COMMENTS': '',
                    'DataOrigin': 'DB',
                    'FromDate': row['FromDate'],
                    'InactiveReason': '',
                    'IsActive': 'YES',
                    'PID': 'DCs',
                    'MURESPONSIBLEGROUPID': zc564_values[0],
                    'PartitionId': 'DCs',
                    'Preference': '-1',
                    'PrefProcessId': PrefProcessId,
                    'ProductLocationId': ProductLocationId,
                    'StatusIcon': '',
                    'StatusMessage': '',
                    'APPLICATION': 'PLS',
                    'ObjectStatus': 'new',
                    'SID': '',
                    'SOURCE': 'ProjectSpecific',
                    'TXID': ''
                }
                new_rows.append(new_row)
            pcc_sourcing_change_df = pd.DataFrame(new_rows)
            pcc_sourcing_change_output_dir = output_dir + "/PCC/" + fileDateName
            output_pcc_sourcing_change_file_path = os.path.join(pcc_sourcing_change_output_dir, "PCC Sourcing Change " + fileDateName + ".xlsx")
            try:
                pcc_sourcing_change_df.to_excel(output_pcc_sourcing_change_file_path, index=False, engine='openpyxl')
            except Exception as e:
                messagebox.showerror("Alert!", e)
                return
            self.progress['value'] = (idx + 1) / total_files * 100
            self.label.config(text = 'PCC Sourcing change successfully')
            self.root.update_idletasks()
            self.label.config(text="Processing Complete")
            self.root.update_idletasks()
            # 弹出确认对话框
            result = messagebox.askyesno("Confirmation", "All processing is done. Open output directory?")
            if result:
                # 如果用户点击了"是"，则打开目录
                os.startfile(output_dir)
                root.withdraw()  # 隐藏主窗口
    def check_thread(self):
        if self.thread.is_alive():
            self.root.after(100, self.check_thread)
        else:
            self.start_button.config(state=tk.NORMAL)
            self.label.config(text="Processing Complete")


if __name__ == "__main__":
    root = tk.Tk()
    app = ExcelProcessorApp(root)
    root.mainloop()
